package cn.jdy.audit.service;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.jdy.audit.entity.EmailRecord;
import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.util.StringUtil;
import cn.jdy.platform.service.ConfService;


/**
 * @title 大数据email后台操作
 * @author jga
 * @since 1-11-2016
 */
@Service
public class EmailRecordService extends BaseDao<EmailRecord>{

  @Autowired
  private ConfService confService;
  
  /**
   * 按条件查询
   * @param sp
   * 
   * @return
   */
  public List<EmailRecord> query(EmailRecord sp){
    if(sp==null) return super.findAll();
    if(sp.getiDisplayLength() > 0){
       return super.find(createFilter(sp),sp.getiDisplayStart(),sp.getiDisplayLength());
      }
    return super.find(createFilter(sp));
  }
   public List<EmailRecord> querypage(EmailRecord sp){
         return super.find(createFilter(sp),sp.getPageNo(),18);
    }
  public Long count(EmailRecord sp){
    return super.count(createFilter(sp));
  }
  /**
   * 拼装条件
   * @param sp
   * 
   * @return
   */
  private DetachedCriteria createFilter(EmailRecord sp){
    DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
    if(!StringUtil.isNullOrEmpty(sp.getCreateTime())&&!",".equals(sp.getCreateTime())) {
      if( sp.getCreateTime().split(",").length>1){
        dc.add(Restrictions.between("createTime", sp.getCreateTime().split(",")[0], sp.getCreateTime().split(",")[1]));
      }else{
        dc.add(Restrictions.like("createTime", sp.getCreateTime(),MatchMode.ANYWHERE));
      }
  }
    if(!StringUtil.isNullOrEmpty(sp.getSrc_account())) {
      dc.add(Restrictions.like("src_account", sp.getSrc_account(),MatchMode.ANYWHERE));
      }
    if(!StringUtil.isNullOrEmpty(sp.getEmail_title())) {
      dc.add(Restrictions.like("email_title", sp.getEmail_title(),MatchMode.ANYWHERE));
      }
    if(!StringUtil.isNullOrEmpty(sp.getUser_ip())) {
      dc.add(Restrictions.like("user_ip", sp.getUser_ip(),MatchMode.ANYWHERE));
      }
    if(!StringUtil.isNullOrEmpty(sp.getEmail_type())) {
      dc.add(Restrictions.like("email_type", sp.getEmail_type()+"",MatchMode.ANYWHERE));

      }
    dc.addOrder(Order.desc("id"));
      return dc;
  }
  /**
   * 删除
   * @param sp:一个EmailRecord对象

  @Transactional
    public void delEmailRecord(EmailRecord sp){
    StringBuilder sql = new StringBuilder("delete ");
      sql.append(entityClass.getSimpleName());
      sql.append(" where ");
          sql.append("and id").append("=").append(sp.getId());
          hibernateTemplate.bulkUpdate(sql.toString(), null);
  }   */

  public  List<EmailRecord> emailRecordCount(EmailRecord sp) {
    String sql="select  id,user_ip,createTime,add_time,email_content,email_title,dest_account,src_account,email_type,count(user_ip) as total from email_record  group by   user_ip";
    if(!StringUtil.isNullOrEmpty(sp.getCreateTime())&&!",".equals(sp.getCreateTime())) {
      if( sp.getCreateTime().split(",").length>1){
        sql+=" having createTime >'"+sp.getCreateTime().split(",")[0]+"'  and createTime<  '"+sp.getCreateTime().split(",")[0]+"'";
      }else{
        sql+=" having like '%"+sp.getCreateTime()+"%'";

      }
  }
    if(!StringUtil.isNullOrEmpty(sp.getSrc_account())) {
      sql+=" having src_account  like '%"+sp.getSrc_account()+"%'";
      }
    if(!StringUtil.isNullOrEmpty(sp.getEmail_title())) {
      sql+=" having email_title like '%"+sp.getEmail_title()+"%'";
      }
    if(!StringUtil.isNullOrEmpty(sp.getUser_ip())) {
      sql+=" having user_ip like '%"+sp.getUser_ip()+"%'";
      }
    if(!StringUtil.isNullOrEmpty(sp.getEmail_type())) {
      sql+=" having email_type like '%"+sp.getEmail_type()+"%'";

      }
    SQLQuery query = em().createSQLQuery(sql).addEntity(EmailRecord.class);
    return query.list();
  }
  /**
   * 每次查询数据先把表里面createTime为null的记录同步add_time的时间
   */
  public void updateSql(){
    String sql="update Email_Record set createTime=FROM_UNIXTIME(add_time) where createTime is NULL";
    SQLQuery sqlQuery = em().createSQLQuery(sql);
    sqlQuery.executeUpdate();
  }
  
}
